//导入数据库
const db=require('../db/index')
const moment=require('moment')

exports.medicineall=(req,res)=>{
    const sql='select * from medicine_view'
    db.query(sql,(err,results)=>{
        if(err) return res.cc(err)
        res.json(results)
    })
}

exports.medicinedelete=(req,res)=>{
    const info=req.body
    const sql='delete from medicine where med_id in (?)'
    db.query(sql,info.id,(err,results)=>{
        if(err) return res.cc(err)
        if(results.affectedRows!=1) return res.cc('failed to delete')
        res.cc('delete successfully',0)
    })
}

exports.medicineupdate=(req,res)=>{
    const info=req.body
    const product_date=moment(info.product_date).format('YYYY-MM-DD')
    const expire_date=moment(info.expire_date).format('YYYY-MM-DD')
    const sql='select * from sort where sort_name=?'
    db.query(sql,info.sort_name,(err,results)=>{
        if(err) return res.cc(err)
        if(results.length!=1) return res.cc('sort not exist')
        const sort_id=results[0].sort_id
        const sql='select * from medicine where med_id=?'
        db.query(sql,info.id,(err,results)=>{
            if(err) return res.cc(err)
            if(results.length!=1) return res.cc('medicine not exist')
            const sql='update medicine set ? where med_id=?'
            db.query(sql,[{med_name:info.med_name,med_num:info.num,med_price:info.price,product_date:product_date,expire_date:expire_date,product_place:info.product_place,sort_id:sort_id},info.id],(err,results)=>{
                if(err)return res.cc(err)
                if(results.affectedRows!=1) return res.cc('failed to update')
                res.cc('update successfully',0)
            })
        })
    })
}

exports.medicineinsert=(req,res)=>{
    const info=req.body
    const product_date=moment(info.product_date).format('YYYY-MM-DD')
    const expire_date=moment(info.expire_date).format('YYYY-MM-DD')
    const sql='select * from sort where sort_name=?'
    db.query(sql,info.sort_name,(err,results)=>{
        if(err) return res.cc(err)
        if(results.length!=1) return res.cc('sort not exist, please add sort before add medicine ')
        const sort_id= results[0].sort_id
        const sql='insert into medicine set ?'
        db.query(sql,{med_name:info.med_name,med_num:info.num,med_price:info.price,product_date:product_date,expire_date:expire_date,product_place:info.product_place,sort_id:sort_id},(err,results)=>{
            if(err)return res.cc(err)
            if(results.affectedRows!=1) return res.cc('failed to insert')
            res.cc('insert successfully',0)
        })
    })
}

exports.medicinefind=(req,res)=>{
    var info=req.body
    var name=info.inputtext
    var start_part=info.start_part
    var end_part=info.end_part
    var start_date=moment(info.start_date).format('YYYY-MM-DD')
    var end_date=moment(info.end_date).format('YYYY-MM-DD')
    var sql=`select * from medicine_view `
    if(info.inputtext.length!=0){
        if(info.selecttype=='name'){
            sql+=` where med_name like '%${name}%' `
        }else if(info.selecttype=='sort'){
            sql+=` where sort_name like '%${name}%' `
        }else if(info.selecttype=='place'){
            sql+=` where product_place like '%${name}%' `
        }
    }
    if(info.start_part.length!=0||info.end_part.length!=0){

        if(info.inputtext.length==0){
            sql+=` where `
        } else if((info.inputtext.length!=0)){
            sql+=` and `
        }

        if(info.parttype=='pricepart'){
            if(info.start_part.length!=0&&info.end_part.length!=0){
                sql+=` med_price >= '${start_part}' and med_price <= '${end_part}' `
            }
            else if(info.start_part.length!=0){
                sql+=` med_price >= '${start_part}' `
            }else if(info.end_part.length!=0){
                sql+=` med_price <= '${end_part}' `
            }
        }else if(info.parttype=='numpart'){
            if(info.start_part.length!=0&&info.end_part.length!=0){
                sql+=` med_num >= '${start_part}' and med_num <= '${end_part}' `
            }
            else if(info.start_part.length!=0){
                sql+=` med_num >= '${start_part}' `
            }else if(info.end_part.length!=0){
                sql+=` med_num <= '${end_part}' `
            }
        }
    }
    if(info.start_date.length!=0||info.end_date.length!=0){
        if(info.inputtext.length!=0||info.start_part.length!=0||info.end_part.length!=0){
            sql+=` and `
        }else{
            sql+=` where `
        }

        if(info.datetype=='pdate'){
            if(info.start_date.length!=0&&info.end_date!=0){
                sql+=` product_date >= '${start_date}' and product_date <= '${end_date}' `
            }
            else if(info.start_date.length!=0){
                sql+=` product_date >= '${start_date}' `
            }else if(info.end_date.length!=0){
                sql+=` product_date <= '${end_date}' `
            }
        }else if(info.datetype=='edate'){
            if(info.start_date.length!=0&&info.end_date.length!=0){
                sql+=` expire_date >= '${start_date}' and expire_date <= '${end_date}' `
            }
            else if(info.start_date.length!=0){
                sql+=` expire_date >= '${start_date}' `
            }else if(info.end_date.length!=0){
                sql+=` expire_date <= '${end_date}' `
            }
        }
    }
    console.log(sql)
    db.query(sql,(err,results)=>{
        if(err) return res.cc(err)
        res.json(results)
    })
}